﻿CREATE PROCEDURE [dbo].[proc_Department_GetChildList]
(
	@departmentid int
)
as
;WITH ChildDepartment(ID,DName,PId,Sort) AS
(
    SELECT ID,DName,ParentID,Sort FROM department WHERE ParentID=@departmentid or id=@departmentid --固定成员
    
    UNION ALL
    SELECT department.ID,department.DName,department.ParentID,department.Sort FROM ChildDepartment AS d
    INNER JOIN department ON d.ID = department.ParentID
)
SELECT distinct DName,ID,PId as ParentID,Sort FROM ChildDepartment order by Sort
